﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using SoTaiChinh.Library.Common.SqlServer;
using Entity = SoTaiChinh.Library.Entity;
namespace SoTaiChinh.Library.Data
{
    public class DanhMuc
    {
        public DanhMuc() { }
        Connection db = new Connection();
        /// <summary>
        /// Inserts a record into the DanhMuc table.
        /// <summary>
        /// <param name="Id"></param>
        /// <param name="DanhMucCha"></param>
        /// <param name="Ten"></param>
        /// <param name="SapXep"></param>
        /// <param name="Tren"></param>
        /// <param name="Trai"></param>
        /// <param name="Phai"></param>
        /// <param name="Duoi"></param>
        /// <param name="Cap"></param>
        /// <param name="TrangThai"></param>
        /// <returns></returns>
        public bool Insert(Entity.DanhMuc entity)
	    {
		    bool boolInsert = false;
		    SqlConnection SqlCnn = db.ReadFromWebConfig();
		    SqlDataAdapter SqlAdp = new SqlDataAdapter();
		    SqlCommand SqlCmd = new SqlCommand();
		    SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		    try
		    {
			    SqlCmd.Transaction = SqlTrans;
			    SqlCmd.Connection = SqlCnn;
			    SqlCmd.CommandText="Proc_DanhMuc_Insert";
			    SqlCmd.CommandType=CommandType.StoredProcedure;
			    SqlCmd.Parameters.Add("@DanhMucCha", SqlDbType.Int).Value=entity.DanhMucCha;
			    SqlCmd.Parameters.Add("@Ten", SqlDbType.NVarChar,255).Value=entity.Ten;
			    SqlCmd.Parameters.Add("@SapXep", SqlDbType.Int).Value=entity.SapXep;
			    SqlCmd.Parameters.Add("@Tren", SqlDbType.Bit).Value=entity.Tren;
			    SqlCmd.Parameters.Add("@Trai", SqlDbType.Bit).Value=entity.Trai;
			    SqlCmd.Parameters.Add("@Phai", SqlDbType.Bit).Value=entity.Phai;
			    SqlCmd.Parameters.Add("@Duoi", SqlDbType.Bit).Value=entity.Duoi;
                SqlCmd.Parameters.Add("@TinTuc", SqlDbType.Bit).Value = entity.TinTuc;
			    SqlCmd.Parameters.Add("@Cap", SqlDbType.Int).Value=entity.Cap;
			    SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value=entity.TrangThai;

			    SqlAdp.InsertCommand = SqlCmd;
			    SqlAdp.InsertCommand.ExecuteNonQuery();
			    SqlCmd.Transaction.Commit();
			    boolInsert = true;
			    SqlCnn.Close();
		    }
		    catch
		    {
			    boolInsert = false;
			    SqlCmd.Transaction.Rollback();
		    }
		    finally
		    {
			    SqlCmd.Parameters.Clear();
			    SqlTrans = null;
		    }
		    if (SqlCmd != null)
			    SqlCmd.Dispose();
		    if (SqlAdp != null)
			    SqlAdp.Dispose();
		    return boolInsert;
	    }

        /// <summary>
        /// Selects a single record from the DanhMuc table.
        /// <summary>
        /// <returns>DataTable</returns>
        public DataTable Select()
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_Select";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }

        /// <summary>
        /// Selects a single record from the DanhMuc table.
        /// <summary>
        /// <returns>DataTable</returns>
        public DataTable SelectPrimaryKey(Entity.DanhMuc entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_SelectPrimaryKey";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public DataTable SelectTheoCap(Entity.DanhMuc entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_SelectTheoCap";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        /// <summary>
        /// Selects a single record from the DanhMuc table.
        /// <summary>
        /// <returns>DataTable</returns>
        public DataTable SelectByDanhMucCha(Entity.DanhMuc entity)
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_SelectByDanhMucCha";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@DanhMucCha", SqlDbType.Int).Value = entity.DanhMucCha;

                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        public DataTable SelectTraiCap()
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_SelectTraiCap";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }

        public DataTable SelectDuoiCap()
        {
            DataTable dt = new DataTable();
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_SelectDuoiCap";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlAdp.SelectCommand = SqlCmd;
                SqlAdp.SelectCommand.ExecuteNonQuery();
                SqlAdp.Fill(dt);
                SqlCmd.Transaction.Commit();
                SqlCnn.Close();
            }
            catch
            {
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return dt;
        }
        /// Updates a record in the DanhMuc table.
        /// <summary>
        /// <param name="Id"></param>
        /// <param name="DanhMucCha"></param>
        /// <param name="Ten"></param>
        /// <param name="SapXep"></param>
        /// <param name="Tren"></param>
        /// <param name="Trai"></param>
        /// <param name="Phai"></param>
        /// <param name="Duoi"></param>
        /// <param name="Cap"></param>
        /// <param name="TrangThai"></param>
        public bool Update(Entity.DanhMuc entity)
        {
            bool boolUpdate = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_Update";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;
                SqlCmd.Parameters.Add("@DanhMucCha", SqlDbType.Int).Value = entity.DanhMucCha;
                SqlCmd.Parameters.Add("@Ten", SqlDbType.NVarChar, 255).Value = entity.Ten;
                SqlCmd.Parameters.Add("@Tren", SqlDbType.Bit).Value = entity.Tren;
                SqlCmd.Parameters.Add("@Trai", SqlDbType.Bit).Value = entity.Trai;
                SqlCmd.Parameters.Add("@Phai", SqlDbType.Bit).Value = entity.Phai;
                SqlCmd.Parameters.Add("@Duoi", SqlDbType.Bit).Value = entity.Duoi;
                SqlCmd.Parameters.Add("@TinTuc", SqlDbType.Bit).Value = entity.TinTuc;
                SqlCmd.Parameters.Add("@Cap", SqlDbType.Int).Value = entity.Cap;
                SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value = entity.TrangThai;

                SqlAdp.UpdateCommand = SqlCmd;
                SqlAdp.UpdateCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolUpdate = true;
                SqlCnn.Close();
            }
            catch
            {
                boolUpdate = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolUpdate;
        }


        /// Updates a record in the DanhMuc table.
        /// <summary>
        /// <param name="Id"></param>
        /// <param name="DanhMucCha"></param>
        /// <param name="Ten"></param>
        /// <param name="SapXep"></param>
        /// <param name="Tren"></param>
        /// <param name="Trai"></param>
        /// <param name="Phai"></param>
        /// <param name="Duoi"></param>
        /// <param name="Cap"></param>
        /// <param name="TrangThai"></param>
        public bool UpdateSapXep(Entity.DanhMuc entity)
        {
            bool boolUpdate = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_Update_SapXep";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;          
                SqlCmd.Parameters.Add("@SapXep", SqlDbType.Int).Value = entity.SapXep;

                SqlAdp.UpdateCommand = SqlCmd;
                SqlAdp.UpdateCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolUpdate = true;
                SqlCnn.Close();
            }
            catch
            {
                boolUpdate = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolUpdate;
        }

        /// <summary>
        /// Deletes a record from the DanhMuc table by a composite primary key.
        /// <summary>
        public bool Delete(Entity.DanhMuc entity)
        {
            bool boolDelete = false;
            SqlConnection SqlCnn = db.ReadFromWebConfig();
            SqlDataAdapter SqlAdp = new SqlDataAdapter();
            SqlCommand SqlCmd = new SqlCommand();
            SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
            try
            {
                SqlCmd.Transaction = SqlTrans;
                SqlCmd.Connection = SqlCnn;
                SqlCmd.CommandText = "Proc_DanhMuc_Delete";
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = entity.Id;

                SqlAdp.DeleteCommand = SqlCmd;
                SqlAdp.DeleteCommand.ExecuteNonQuery();
                SqlCmd.Transaction.Commit();
                boolDelete = true;
                SqlCnn.Close();
            }
            catch
            {
                boolDelete = false;
                SqlCmd.Transaction.Rollback();
            }
            finally
            {
                SqlCmd.Parameters.Clear();
                SqlTrans = null;
            }
            if (SqlCmd != null)
                SqlCmd.Dispose();
            if (SqlAdp != null)
                SqlAdp.Dispose();
            return boolDelete;
        }
    }
}
